package com.bestv.search.common.dao.hibernate;

import com.bestv.search.common.dao.SearchKeywordHistoryDao;
import com.bestv.search.common.model.SearchKeywordHistory;
import com.bestv.search.common.model.TopNCache;
import com.bestv.search.common.util.StringConst;
import org.hibernate.*;
import org.hibernate.transform.Transformers;
import org.springframework.orm.hibernate3.HibernateCallback;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class SearchKeywordHistoryDaoHibernate extends GenericDaoHibernate<SearchKeywordHistory, Long> implements
        SearchKeywordHistoryDao {
    /**
     * @param type 统计的类型，分为日(daily), 周(weekly), 月(monthly), 季(quarter), 总(total)
     * @return
     */

    @Override
    public List<TopNCache> getKeywordTopNCache(String topNType) {
        int defaultTopCount = 100;
        if (StringConst.DAILY.equals(topNType) || StringConst.WEEKLY.equals(topNType)
                || StringConst.MONTHLY.equals(topNType) || StringConst.QUARTER.equals(topNType)) {
            return getKeywordTopNCacheByDate(defaultTopCount, topNType);
        } else if (StringConst.TOTAL.equals(topNType)) {
            return getKeywordTotalTopNCache(defaultTopCount);
        } else {
            log.error("Unknow TopNCache type " + topNType + ", just support daily/weekly/monthly/quarter/total.");
            return null;
        }
    }

    @Override
    public void removeByDate(final Date date) {
        getHibernateTemplate().execute(new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                String hql = "delete SearchKeywordHistory where date =?";
                Query query = session.createQuery(hql);
                query.setDate(0, date);
                return query.executeUpdate();
            }
        });
    }

    /**
     * 获取搜索关键字的历史统计信息
     *
     * @param keyword   , 搜索关键字
     * @param startDate , 开始日期
     * @param endDate   , 结束日期
     * @param type      , 统计的类型，分为日(daily), 周(weekly), 月(monthly), 季(quarter)
     * @return 统计结果
     */
    @Override
    public List<String[]> getSearchKeywordHistoryByKeyword(String keyword, Date startDate, Date endDate, String type) {
        String sql = null;
        if ("weekly".equals(type)) {
            sql = "select DATE_FORMAT(date,'%Y年%u周') as weeks, sum(searchCount) from searchKeyword_History"
                    + " where keyword =? and date between ? and ? group by weeks order by weeks asc";
        } else if ("monthly".equals(type)) {
            sql = "select DATE_FORMAT(date,'%Y年%m月') as months, sum(searchCount) from searchKeyword_History"
                    + " where keyword =? and date between ? and ? group by months order by months asc";
        } else if ("quarter".equals(type)) {
            sql = "select concat(DATE_FORMAT(date,'%Y年'),if(DATE_FORMAT(date,'%m')<=3 ,'01季',"
                    + " if(DATE_FORMAT(date,'%m')<=6,'02季',if(DATE_FORMAT(date,'%m')<=9,'03季','04季')))) as quarter, "
                    + " sum(searchCount) from searchKeyword_History where keyword=? and date between ? and ?"
                    + " group by quarter order by quarter asc ";
        } else {
            sql = "select DATE_FORMAT(date,'%Y年%m月%d日') as day,sum(searchCount) from searchKeyword_History where keyword = ? and date between ? and ?"
                    + " group by date order by date asc ";
        }

        @SuppressWarnings("rawtypes")
        List list = null;
        Session session = null;
        List<String[]> results = new ArrayList<String[]>();
        try {
            session = getSession();
            Query query = session.createSQLQuery(sql);
            query.setString(0, keyword);
            query.setDate(1, startDate);
            query.setDate(2, endDate);

            list = query.list();
            for (Object obj : list) {
                // gets one row
                Object[] row = (Object[]) obj;

                String[] record = new String[3];
                record[0] = keyword;
                record[1] = row[0].toString();
                record[2] = row[1].toString();

                results.add(record);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (session != null)
                releaseSession(session);
        }

        return results;
    }

    @Override
    public Date getMaxDate() {
        String hql = "select date from  SearchKeywordHistory order by date desc";

        ScrollableResults rs = null;
        Session session = null;
        Date maxDate = null;
        try {
            session = getSession();
            Query query = session.createQuery(hql);
            query.setMaxResults(1);
            rs = query.scroll();
            if (rs.next()) {
                maxDate = rs.getDate(0);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {

            if (rs != null)
                rs.close();
            if (session != null)
                releaseSession(session);
        }
        return maxDate;
    }

    @SuppressWarnings({"unchecked"})
    private List<TopNCache> getKeywordTopNCacheByDate(int topN, String topNType) {
        String subType = StringConst.KEYWORD;
        String sql = "{call getTopNKeywords(?,?)}";

        Session session = null;
        try {
            session = getSession();
            Query query = session.createSQLQuery(sql).addScalar("rankNo", Hibernate.INTEGER)
                    .addScalar("name", Hibernate.STRING).addScalar("thisCount", Hibernate.INTEGER)
                    .addScalar("lastRankNo", Hibernate.INTEGER).addScalar("lastCount", Hibernate.INTEGER)
                    .setResultTransformer(Transformers.aliasToBean(TopNCache.class));

            query.setString(0, topNType);
            query.setInteger(1, topN);

            List<TopNCache> list = query.list();
            if (list != null) {
                for (TopNCache topnCache : list) {
                    topnCache.setType(topNType);
                    topnCache.setSubType(subType);
                    topnCache.setId(Math.abs(topNType.hashCode()) + Math.abs(subType.hashCode())
                            + String.valueOf(topnCache.getRankNo()));
                    topnCache.setIncreaseNo(topnCache.getLastRankNo() - topnCache.getRankNo());
                    topnCache.setUpdateTime(new Date());
                }
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (session != null)
                releaseSession(session);
        }
        return null;
    }

    // 获取本时间段和上一时间段的关键字排名
    @SuppressWarnings({"unchecked"})
    private List<TopNCache> getKeywordTotalTopNCache(int topN) {
        String type = StringConst.TOTAL;
        String subType = StringConst.KEYWORD;
        String sql = "select keyword as name, sum(searchHistory.searchCount) as thisCount"
                + " from searchKeyword_History as searchHistory group by keyword "
                + " order by sum(searchHistory.searchCount) desc, keyword asc limit 0, ?";

        Session session = null;
        try {
            session = getSession();
            Query query = session.createSQLQuery(sql).addScalar("name", Hibernate.STRING)
                    .addScalar("thisCount", Hibernate.INTEGER)
                    .setResultTransformer(Transformers.aliasToBean(TopNCache.class));

            query.setInteger(0, topN);

            List<TopNCache> list = query.list();
            if (list != null) {
                int rankNo = 0;
                for (TopNCache topnCache : list) {
                    topnCache.setRankNo(++rankNo);
                    topnCache.setType(type);
                    topnCache.setSubType(subType);
                    topnCache.setId(Math.abs(type.hashCode()) + Math.abs(subType.hashCode())
                            + String.valueOf(topnCache.getRankNo()));
                    topnCache.setUpdateTime(new Date());
                }
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (session != null)
                releaseSession(session);
        }
        return null;
    }

    @Override
    public void deleteHistroyByKeyword(String keyword) {
        String hql = "delete from SearchKeywordHistory where keyword = ?";
        Session session = null;
        try {
            session = getSession();
            Query query = session.createQuery(hql);
            query.setString(0, keyword);
            query.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (session != null)
                releaseSession(session);
        }
    }
}